from sqlalchemy import Column, Integer, String, Text 
from sqlalchemy import ForeignKey
from sqlalchemy import create_engine 
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, backref
from sqlalchemy.orm import sessionmaker 

Base = declarative_base()
engine = create_engine('sqlite:///addresses_1.db', echo=False)
metadata = Base.metadata


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)
    addresses = relation('Address', order_by='Address.id', backref="user")
    # above "user" denotes the dot notation to put after addresses to get back here

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname,
                                            self.password)

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

    # user = relation(User, backref=backref('addresses', order_by=id))

    def __init__(self, email_address):
        self.email_address = email_address

    def __repr__(self):
        return "<Address('%s')>" % self.email_address
    
def session_open():
    Session = sessionmaker(bind=engine)
    return Session()


if __name__ == '__main__':
###  CREATE TABLES / SESSION
#    metadata.create_all(engine)

    s = session_open()
### working example from docs
#    jack = User('Jack', 'Jack Jones', 'sads')
#    print 10, jack
#    print 20, jack.addresses
#    # jack.addresses = [Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')]
#    # each time you add an address,new records are created
#    # print 30, jack.addresses[1]
#    # print 40, jack.addresses[1].user
#    s.add(jack)
#    s.commit()
#    print 50, jack.addresses
#    print s.query(User).all()    

### works to add addresses
#    print 20 * '==='
#    a = Address('fred@fred.com')
#    s.add(a)
#    s.commit()
#    print s.query(Address).all() 

### can we add a specified address
#    a = s.query(Address).filter("email_address = 'tony@tony.com'").one()
#    print a
#    u = s.query(User).filter("name = 'tony'").one()
#    print u, u.addresses
#    # u.addresses = [a]
#    print u, u.addresses
#    s.commit()

    for a in s.query(Address).all():
        print 3 * '++++'
        print 60, a
        print 70, a.user_id
        print 80, a.user.id
        print 90, a.user.name
        print 100, a.user.addresses

#    for a in s.query(Address).filter('email_address = "fred@fred.com"').all():
#        print 100, a
#        s.delete(a)
#    s.commit()    

    